package smb.service;

import lombok.AllArgsConstructor;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import smb.dao.ScoreDao;
import smb.dao.SectionDao;
import smb.dao.StudentDao;
import smb.domain.Course;
import smb.domain.Score;
import smb.domain.Section;
import smb.domain.Student;
import smb.mapper.CourseMapper;
import smb.mapper.ScoreMapper;
import smb.mapper.SectionMapper;
import smb.vo.*;

import javax.persistence.EntityNotFoundException;
import java.sql.Timestamp;
import java.time.Instant;
import java.util.List;
import java.util.Optional;

@Service
@AllArgsConstructor
public class SectionService {
    private JdbcTemplate jdbcTemplate;
    private ScoreDao scoreDao;
    private ScoreMapper scoreMapper;
    private SectionDao sectionDao;
    private StudentDao studentDao;
    private SectionMapper sectionMapper;
    private CourseMapper courseMapper;

    public CourseSection getSection(String id) {
        Optional<Section> section = sectionDao.findById(id);
        if (section.isPresent()) {
            SectionVo sectionVo = sectionMapper.sectionToSectionVo(section.get());
            CourseVo courseVo = courseMapper.courseToCourseVo(section.get().getCourse());
            return new CourseSection(sectionVo, courseVo);
        } else {
            throw new EntityNotFoundException();
        }
    }

    public List<StudentScore> getStudents(String id, String order) {
        String sql =
                "SELECT student.id, student.name, student.sex,\n" +
                "       score.usual, score.midterm, score.experiment, score.terminal, score.total, score.time_stamp\n" +
                "FROM section\n" +
                "JOIN score ON section.id = score.sec_id\n" +
                "JOIN student ON score.stu_id = student.id\n" +
                "WHERE section.id = ?\n";
        switch (order) {
            case "id":
                sql += "ORDER BY student.id";
                break;
            case "name":
                sql += "ORDER BY student.name";
                break;
            case "score":
                sql += "ORDER BY score.total DESC ";
                break;
        }
        return jdbcTemplate.query(sql, new Object[]{id}, new StudentScore.rowMapper());
    }

    public void postScore(String sectionId, String studentId, ScoreVo scoreVo) {
        List<Score> scores = scoreDao.findAllBySectionIdAndStudentId(sectionId, studentId);
        if (scores.size() > 0) {
            Score score = scores.get(0);
            scoreVo.setTimestamp(Timestamp.from(Instant.now()));
            scoreMapper.updateScore(scoreVo, score);
            scoreDao.save(score);
        } else {
            throw new EntityNotFoundException();
        }
    }

    public ScoreDistribution getDistribution(String id) {
        String sql = "SELECT\n" +
                "CASE\n" +
                "WHEN total is null OR total < 60 THEN 0\n" +
                "WHEN 60 <= total AND total < 70 THEN 1\n" +
                "WHEN 70 <= total AND total < 80 THEN 2\n" +
                "WHEN 80 <= total AND total < 90 THEN 3\n" +
                "WHEN 90 <= total THEN 4\n" +
                "END AS RANGES,\n" +
                "COUNT(*)\n" +
                "FROM\n" +
                "score\n" +
                "WHERE sec_id = ?\n" +
                "GROUP BY RANGES\n" +
                "ORDER BY RANGES;";
        ScoreDistribution scoreDistribution = new ScoreDistribution(0, 0, 0, 0, 0, 0);
        jdbcTemplate.query(sql, new Object[]{id}, resultSet -> {
            switch (resultSet.getInt(1)) {
                case 0:
                    scoreDistribution.set_0_60(resultSet.getInt(2));
                    break;
                case 1:
                    scoreDistribution.set_60_70(resultSet.getInt(2));
                    break;
                case 2:
                    scoreDistribution.set_70_80(resultSet.getInt(2));
                    break;
                case 3:
                    scoreDistribution.set_80_90(resultSet.getInt(2));
                    break;
                case 4:
                    scoreDistribution.set_90_100_(resultSet.getInt(2));
                    break;
            }
            scoreDistribution.setTotal(scoreDistribution.getTotal() + resultSet.getInt(2));
        });
        return scoreDistribution;
    }
}
